import os,shutil,function,time
import mysql.connector
from datetime import datetime

t0=time.time()

mydb = mysql.connector.connect(
  host="192.168.10.81",
  user="root",
  passwd="",
  database="xiaocheng"
)
# 获取最大id
mycursor = mydb.cursor()
mycursor.execute("select max(id) from pattern") 
myresult = mycursor.fetchone()
begin_id=myresult[0]+1
print(begin_id)

print(time.time()-t0)
t0=time.time()

pic_path = 'D:/Desk/系统图库/烫画集/0-原始镜像/'
pic_paths = function.get_file_paths(pic_path)
print('FILE:',len(pic_paths))

print(time.time()-t0)
t0=time.time()

db_paths = []
mycursor.execute("select path from pattern")
myresult = mycursor.fetchall()
for x in myresult:
    db_paths.append(x[0])

print('DB:',len(db_paths))
now = datetime.now()
formatted_date = now.strftime("%Y-%m-%d %H:%M:%S")

datas=[]
i=0
for pic_path in pic_paths:
    file=os.path.basename(pic_path)
    if pic_path not in db_paths and file.lower().endswith(('.png')):
        name='TH' +str(begin_id + 10000 + i)
        pic=name+os.path.splitext(file)[1]
        name_original=os.path.splitext(file)[0]
        dict=[]
        dict.append(name)
        dict.append(pic)
        dict.append(pic_path)
        dict.append(name_original)
        dict.append(formatted_date) 
        dict.append('未处理') 
        datas.append(tuple(dict))
        # print(tuple(dict))
        i+=1
print(time.time()-t0)
t0=time.time()
print(i)

# # 插入数据
# sql = "insert into pattern (name,pic,path,name_original,t_add,operate) values (%s,%s,%s,%s,%s,%s)" 
# mycursor.executemany(sql, datas) 
# mydb.commit()    # 数据表内容有更新，必须使用到该语句
 
# print(mycursor.rowcount, "记录插入成功。")

# mycursor.close()
# mydb.close()

# for path in db_paths:
#     if path not in pic_paths:
#         sql = "DELETE FROM pingpu WHERE path = '"+path+"'"
#         mycursor.execute(sql)
#         mydb.commit()
#         print(mycursor.rowcount, " 条记录删除")


